/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/07/31
 */
package vn.smartware.co.server.dao.search;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.framework.form.CommonSearchForm;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.sql.factory.AbstractCommonSearchSQLManager;

/**
 * @author thuctt
 * CommonSearchSQLManager
 */
public class CommonSearchSQLManager extends AbstractCommonSearchSQLManager {

	private static Log log = LogFactory.getLog(CommonSearchSQLManager.class);
	
	/**
	 * get drink list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetDrinkListSQL(CommonSearchForm form, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" DRINK.DRINK_NO, ")
		.append(" DRINK.NAME, ")
		.append(" DRINK.DRINK_TYPE, ")
		.append(" CLS1.NAME AS DRINK_TYPE_NAME,")
		.append(" DRINK.DESCRIPTION, ")
		.append(" PRICE.PRICE_NO, ")
		.append(" PRICE.PRICE, ")
		.append(" PRICE.MONEY_TYPE, ")
		.append(" CLS2.NAME AS MONEY_TYPE_NAME,")
		.append(" DRINK.UNIT_CD, ")
		.append(" UNIT.NAME AS UNIT_NAME ")
		
		.append(" FROM MSTDRINK DRINK ")
		
		.append(" LEFT OUTER JOIN MSTDRINKPRICE PRICE ")
		.append(" ON PRICE.DRINK_NO = DRINK.DRINK_NO AND PRICE.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS1 ")
		.append(" ON CLS1.KEY1 = 'DRINK_TYPE' AND CLS1.VALUE = DRINK.DRINK_TYPE ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS2 ")
		.append(" ON CLS2.KEY1 = 'MONEY_TYPE' AND CLS2.VALUE = PRICE.MONEY_TYPE ")
		
		.append(" LEFT OUTER JOIN MSTUNIT UNIT ")
		.append(" ON UNIT.UNIT_CD = DRINK.UNIT_CD AND UNIT.DELETE_DATE IS NULL ")
		
		.append(" WHERE DRINK.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getNoStart())){
			buf.append(" AND DRINK.DRINK_NO >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getNoEnd())){
			buf.append(" AND DRINK.DRINK_NO <= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getPriceStart())){
			buf.append(" AND PRICE.PRICE >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getPriceEnd())){
			buf.append(" AND PRICE.PRICE <= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getDrinkType())){
			buf.append(" AND DRINK.DRINK_TYPE = ? ");
		}
		
		if (!Utils.isNullEmpty(form.getCurrencyCd())){
			buf.append(" AND PRICE.MONEY_TYPE = ? ");
		}
		
		if (!Utils.isNullEmpty(form.getName())){
			buf.append(" AND DRINK.NAME LIKE ? ");
		}
		
		buf.append(" ORDER BY DRINK.DRINK_TYPE, DRINK.DRINK_NO, PRICE.MONEY_TYPE ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (!Utils.isNullEmpty(form.getNoStart())){
				ps.setInt(i++, Integer.parseInt(form.getNoStart()));
				log.info("DRINK_NO >= " + form.getNoStart());
			}
			
			if (!Utils.isNullEmpty(form.getNoEnd())){
				ps.setInt(i++, Integer.parseInt(form.getNoEnd()));
				log.info("DRINK_NO <= " + form.getNoEnd());
			}
			
			if (!Utils.isNullEmpty(form.getPriceStart())){
				ps.setInt(i++, Integer.parseInt(form.getPriceStart()));
				log.info("PRICE >= " + form.getPriceStart());
			}
			
			if (!Utils.isNullEmpty(form.getPriceEnd())){
				ps.setInt(i++, Integer.parseInt(form.getPriceEnd()));
				log.info("PRICE <= " + form.getPriceEnd());
			}
			
			if (!Utils.isNullEmpty(form.getDrinkType())){
				ps.setString(i++, form.getDrinkType());
				log.info("DRINK_TYPE = " + form.getDrinkType());
			}
			
			if (!Utils.isNullEmpty(form.getCurrencyCd())){
				ps.setString(i++, form.getCurrencyCd());
				log.info("MONEY_TYPE = " + form.getCurrencyCd());
			}
			
			if (!Utils.isNullEmpty(form.getName())){
				ps.setString(i++, "%" + form.getName() + "%");
				log.info("NAME LIKE " + "%" + form.getName() + "%");
			}
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * get food list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetFoodListSQL(CommonSearchForm form, LoginDTO user, Connection conn)
			throws ServerException {
	
		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" FOOD.FOOD_NO, ")
		.append(" FOOD.NAME, ")
		.append(" FOOD.DESCRIPTION, ")
		.append(" PRICE.PRICE_NO, ")
		.append(" PRICE.PRICE, ")
		.append(" PRICE.MONEY_TYPE, ")
		.append(" CLS2.NAME AS MONEY_TYPE_NAME,")
		.append(" FOOD.UNIT_CD, ")
		.append(" UNIT.NAME AS UNIT_NAME ")
		
		.append(" FROM MSTFOOD FOOD ")
		
		.append(" LEFT OUTER JOIN MSTFOODPRICE PRICE ")
		.append(" ON PRICE.FOOD_NO = FOOD.FOOD_NO AND PRICE.DELETE_DATE IS NULL ")

		.append(" LEFT OUTER JOIN TYPECLASS CLS2 ")
		.append(" ON CLS2.KEY1 = 'MONEY_TYPE' AND CLS2.VALUE = PRICE.MONEY_TYPE ")
		
		.append(" LEFT OUTER JOIN MSTUNIT UNIT ")
		.append(" ON UNIT.UNIT_CD = FOOD.UNIT_CD AND UNIT.DELETE_DATE IS NULL ")
		
		.append(" WHERE FOOD.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getNoStart())){
			buf.append(" AND FOOD.FOOD_NO >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getNoEnd())){
			buf.append(" AND FOOD.FOOD_NO <= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getSearchName())){
			buf.append(" AND FOOD.NAME LIKE ? ");
		}
		
		if (!Utils.isNullEmpty(form.getPriceStart())){
			buf.append(" AND PRICE.PRICE >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getPriceEnd())){
			buf.append(" AND PRICE.PRICE <= ? ");
		}	
		
		if (!Utils.isNullEmpty(form.getCurrencyCd())){
			buf.append(" AND PRICE.MONEY_TYPE = ? ");
		}
	
		buf.append(" ORDER BY FOOD.FOOD_NO, PRICE.MONEY_TYPE ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (!Utils.isNullEmpty(form.getNoStart())){
				ps.setInt(i++, Integer.parseInt(form.getNoStart()));
				log.info("FOOD_NO >= " + form.getNoStart());
			}
			
			if (!Utils.isNullEmpty(form.getNoEnd())){
				ps.setInt(i++, Integer.parseInt(form.getNoEnd()));
				log.info("FOOD_NO <= " + form.getNoEnd());
			}
			
			if (!Utils.isNullEmpty(form.getSearchName())){
				ps.setString(i++, "%" + form.getSearchName() + "%");
				log.info("NAME = " + form.getSearchName());
			}
			
			if (!Utils.isNullEmpty(form.getPriceStart())){
				ps.setInt(i++, Integer.parseInt(form.getPriceStart()));
				log.info("PRICE >= " + form.getPriceStart());
			}
			
			if (!Utils.isNullEmpty(form.getPriceEnd())){
				ps.setInt(i++, Integer.parseInt(form.getPriceEnd()));
				log.info("PRICE <= " + form.getPriceEnd());
			}
			
			if (!Utils.isNullEmpty(form.getCurrencyCd())){
				ps.setString(i++, form.getCurrencyCd());
				log.info("MONEY_TYPE = " + form.getCurrencyCd());
			}
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
	
	/**
	 * get room list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @throws ServerException
	 */
	public void makeGetRoomListSQL(CommonSearchForm form, LoginDTO user, Connection conn)
			throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" ROOM.ROOM_NO, ")
		.append(" ROOM.ROOM_NAME, ")
		.append(" ROOM.ROOM_TYPE, ")
		.append(" CLS1.NAME AS ROOM_TYPE_NAME, ")
		.append(" ROOM.DESCRIPTION, ")
		.append(" ROOM.STATUS, ")
		.append(" CLS2.NAME AS STATUS_NAME, ")
		.append(" PRICE.PRICE_NO, ")
		.append(" PRICE.RENT_TYPE, ")
		.append(" CLS3.NAME AS RENT_TYPE_NAME, ")
		.append(" PRICE.MONEY_TYPE, ")
		.append(" CLS4.NAME AS MONEY_TYPE_NAME, ")
		.append(" PRICE.PRICE ")
		
		.append(" FROM MSTROOM ROOM ")
		
		.append(" LEFT OUTER JOIN MSTROOMPRICE PRICE ")
		.append(" ON PRICE.ROOM_NO = ROOM.ROOM_NO AND PRICE.DELETE_DATE IS NULL ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS1 ")
		.append(" ON CLS1.KEY1 = 'ROOM_TYPE' AND CLS1.VALUE = ROOM.ROOM_TYPE ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS2 ")
		.append(" ON CLS2.KEY1 = 'ROOM_STATUS' AND CLS2.VALUE = ROOM.STATUS ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS3 ")
		.append(" ON CLS3.KEY1 = 'RENT_TYPE' AND CLS3.VALUE = PRICE.RENT_TYPE ")
		
		.append(" LEFT OUTER JOIN TYPECLASS CLS4 ")
		.append(" ON CLS4.KEY1 = 'MONEY_TYPE' AND CLS4.VALUE = PRICE.MONEY_TYPE ")
		
		.append(" WHERE ROOM.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getNoStart())){
			buf.append(" AND ROOM.ROOM_NO >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getNoEnd())){
			buf.append(" AND ROOM.ROOM_NO <= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getSearchName())){
			buf.append(" AND ROOM.ROOM_NAME LIKE ? ");
		}		
		
		if (!Utils.isNullEmpty(form.getRoomType())){
			buf.append(" AND ROOM.ROOM_TYPE = ? ");
		}
		
		if (!Utils.isNullEmpty(form.getRoomStatus())){
			buf.append(" AND ROOM.STATUS = ? ");
		}
		
		buf.append(" ORDER BY ROOM.ROOM_NAME, PRICE.RENT_TYPE, PRICE.MONEY_TYPE ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (!Utils.isNullEmpty(form.getNoStart())){
				ps.setInt(i++, Integer.parseInt(form.getNoStart()));
				log.info("ROOM_NO >= " + form.getNoStart());
			}
			
			if (!Utils.isNullEmpty(form.getNoEnd())){
				ps.setInt(i++, Integer.parseInt(form.getNoEnd()));
				log.info("ROOM_NO <= " + form.getNoEnd());
			}
			
			if (!Utils.isNullEmpty(form.getSearchName())){
				ps.setString(i++, "%" + form.getSearchName() + "%");
				log.info("ROOM_NAME = " + "%" + form.getSearchName() + "%");
			}
			
			if (!Utils.isNullEmpty(form.getRoomType())){
				ps.setString(i++, form.getRoomType());
				log.info("ROOM_TYPE = " + form.getRoomType());
			}
			if (!Utils.isNullEmpty(form.getRoomStatus())){
				ps.setString(i++, form.getRoomStatus());
				log.info("STATUS = " + form.getRoomStatus());
			}
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}

	/**
	 * makeGetCustomerListSQL
	 * @param form
	 * @param user
	 * @param conn
	 * @throws ServerException
	 */
	@Override
	public void makeGetCustomerListSQL(CommonSearchForm form, LoginDTO user,
			Connection conn) throws ServerException {

		StringBuffer buf = new StringBuffer();
		buf.append(" SELECT ")
		
		.append(" CUS.CUSTOMER_NO, ")
		.append(" CUS.NAME, ")
		.append(" CUS.ID_NUMBER, ")
		.append(" CUS.BIRTHDAY, ")
		.append(" CUS.TEL, ")
		.append(" CUS.EMAIL, ")
		.append(" CUS.ADDRESS_CD, ")
		.append(" ADDR.HOUSE_NO, ")
		.append(" ADDR.STREET, ")
		.append(" ADDR.WARD, ")
		.append(" ADDR.DISTRICT, ")
		.append(" ADDR.CITY, ")
		.append(" ADDR.NATIONALITY ")
		
		.append(" FROM MSTCUSTOMER CUS ")
		
		.append(" LEFT OUTER JOIN MSTADDRESS ADDR ")
		.append(" ON ADDR.ADDRESS_CD = CUS.ADDRESS_CD AND ADDR.DELETE_DATE IS NULL ")
		
		.append(" WHERE CUS.DELETE_DATE IS NULL ")
		;
		
		if (!Utils.isNullEmpty(form.getNoStart())){
			buf.append(" AND CUS.CUSTOMER_NO >= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getNoEnd())){
			buf.append(" AND CUS.CUSTOMER_NO <= ? ");
		}
		
		if (!Utils.isNullEmpty(form.getSearchName())){
			buf.append(" AND CUS.NAME LIKE ? ");
		}		
		
		if (!Utils.isNullEmpty(form.getIdNumber())){
			buf.append(" AND CUS.ID_NUMBER LIKE ? ");
		}
		
		if (!Utils.isNullEmpty(form.getTel())){
			buf.append(" AND CUS.TEL LIKE ? ");
		}
		
		if (!Utils.isNullEmpty(form.getEmail())){
			buf.append(" AND CUS.EMAIL LIKE ? ");
		}
		
		buf.append(" ORDER BY CUS.CUSTOMER_NO ");
		
		String sql = buf.toString();
		log.info("SQL = " + sql);
		PreparedStatement ps = null;
		
		try {
			precompilePreparedStatement(sql, conn);
			ps = getPreparedStatement();
			int i = 1;
			
			if (!Utils.isNullEmpty(form.getNoStart())){
				ps.setInt(i++, Integer.parseInt(form.getNoStart()));
				log.info("CUSTOMER_NO >= " + form.getNoStart());
			}
			
			if (!Utils.isNullEmpty(form.getNoEnd())){
				ps.setInt(i++, Integer.parseInt(form.getNoEnd()));
				log.info("CUSTOMER_NO <= " + form.getNoEnd());
			}
			
			if (!Utils.isNullEmpty(form.getSearchName())){
				ps.setString(i++, "%" + form.getSearchName() + "%");
				log.info("NAME = " + "%" + form.getSearchName() + "%");
			}
			
			if (!Utils.isNullEmpty(form.getIdNumber())){
				ps.setString(i++, "%" + form.getIdNumber() + "%");
				log.info("ID_NUMBER = " + "%" + form.getIdNumber() + "%");
			}
			
			if (!Utils.isNullEmpty(form.getTel())){
				ps.setString(i++, "%" + form.getTel() + "%");
				log.info("TEL = " + "%" + form.getTel() + "%");
			}
			
			if (!Utils.isNullEmpty(form.getEmail())){
				ps.setString(i++, "%" + form.getEmail() + "%");
				log.info("EMAIL = " + "%" + form.getEmail() + "%");
			}
			
			precompilePreparedStatement(ps);
			
		} catch (SQLException e) {
			log.error("SQLException. ", e);
			throw new ServerException();
		}
	}
}
